<?php

class Report_IndexController extends Zend_Controller_Action
{
	protected $_inbound;
	protected $_inbound_detail;
	public $inbound_warehouse_group_code;
	public $string_warehouse_group_name;

	public function init()
	{
		//tạo đối tượng chính của module
		$this->_inbound = new Admin_Model_TmpInbound ();
		$this->_inbound_detail = new Admin_Model_InboundDetail ();
		
		//lấy tham số
		$this->inbound_warehouse_group_code = $this->_request->getParam ( "inbound_warehouse_group_code" );
		
		//lấy tên nhóm kho
		switch ($this->inbound_warehouse_group_code)
		{
			case 'NVL' :
				$this->string_warehouse_group_name = 'nguyên vật liệu';
				break;
			case 'BB' :
				$this->string_warehouse_group_name = 'bao bì';
				break;
			case 'TP' :
				$this->string_warehouse_group_name = 'thành phẩm';
				break;
		}
		
		//truyền dữ liệu ra view
		$this->view->string_warehouse_group_name = $this->string_warehouse_group_name;
		$this->view->inbound_warehouse_group_code = $this->inbound_warehouse_group_code;
	}

	//Action ho tro xuat excel
	public function reportAction()
	{
		//Mac dinh dung layout excel
		$this->getHelper ( 'Layout' )->setLayout ( 'excel' );
		//$this->getHelper('Layout')->disableLayout();
		

		//Lay tham so, khai bao bien.
		$front = Zend_Controller_Front::getInstance ();
		$front_request = $front->getRequest ();
		$request = $this->getRequest ();
		
		$type = $request->getParam ( 'type', '' ); //Xac dinh loai bao cao
		$data = $request->getParam ( 'data' ); //Du lieu
		$params = $request->getParams ();
		
		//Xu ly
		//Truong hop chua xac dinh loai bao cao
		if (( string ) $type == '')
		{
			throw new Zend_Controller_Action_Exception ( "Report type was not set." );
		}
		
		//Truyen ra VIEW
		$this->view->data = $data;
		$this->view->type = $type;
		$this->view->params = $params;
	}

	//bao cao ton kho
	public function inventoryAction()
	{
		//lấy tham số
		$request = $this->_request;
		$adminAction = $request->getParam ( 'adminAction', '' );
		$layout = $request->getParam ( 'layout', '' );
		$product_group_code = $request->getParam ( 'product_group_code', '' );
		$inventory_product_code = $request->getParam ( 'inventory_product_code', '' );
		$inventory_lot_code = $request->getParam ( 'inventory_lot_code', '' );
		$unit_code = $request->getParam ( 'unit_code', '' );
		
		//tao form
		$form = new Report_Form_Inventory ( $this );
		
		//lấy danh sách hàng hóa
		$product = new Admin_Model_Product ();
		$select_products = $product->fetchAllBase ( null, "product_code ASC" );
		$form->inventory_product_code->setMultiOptions ( array ('' => '- Chọn -' ) + $product->toArray2 ( $select_products, "product_code", "\$row->product_code - \$row->product_name" ) );
		
		//select nhóm kho
		$form->warehouse_group_code->setMultiOptions ( array ('' => "- Chọn -" ) + Zend_Registry::get ( 'warehouse-group' )->warehouse_group_code->toArray () );
		
		//select nhóm hàng hóa
		$product_group = new Admin_Model_ProductGroup ();
		$options = $product_group->fetchOptions ( $product_group->fetchGroups () );
		$form->product_group_code->setMultiOptions ( array ('' => '- Chọn -' ) + $options );
		
		//select dvt
		$unit = new Admin_Model_Unit ();
		$units = $unit->fetchAllBase ( null, "unit_code ASC" );
		$form->unit_code->setMultiOptions ( array ('' => '- Chọn -' ) + $unit->toArray2 ( $units, "unit_code", "\$row->unit_code - \$row->unit_name" ) );
		
		//các cột cần lấy dữ liệu
		$select = " 
		SELECT DISTINCT
			`inventory_id`
			, product_code AS `inventory_product_code`
			, `inventory_product_unit_code`
			, `inventory_quantity`
			, `inventory_lot_code`
			, `inventory_product_time`
			, `inventory_expire_time`
			, `product_name`
			, `location_name`";
		if ($unit_code != "")
		{
			$select .= "
			,(CASE product_instore_max WHEN -1 THEN 'TK max' ELSE 
			(CASE COALESCE(change_2.`change_rate`,0) WHEN 0 THEN CONCAT(product_instore_unit_code,'->$unit_code')  ELSE change_2.`change_rate`*product_instore_max END) END) AS `product_instore_max`
			,(CASE product_instore_min WHEN -1 THEN 'TK min' ELSE 
			(CASE COALESCE(change_2.`change_rate`,0) WHEN 0 THEN CONCAT(product_instore_unit_code,'->$unit_code') ELSE change_2.`change_rate`*product_instore_min END) END) AS `product_instore_min`
			,(CASE COALESCE(change_1.`change_rate`,0) WHEN 0 THEN CONCAT(inventory_product_unit_code,'->$unit_code') 
			ELSE COALESCE(change_1.`change_rate`,0)*inventory_quantity END) AS `inventory_quantity_after_change` ";
		}
		$select .= "
		FROM tbl_product 
			INNER JOIN tbl_product_group ON product_group_code = product_product_group_code
			LEFT JOIN tbl_inventory ON product_code = inventory_product_code	
			LEFT JOIN tbl_location ON inventory_location_code = location_code
		";
		if ($unit_code != "")
		{
			$select .= " LEFT JOIN tbl_change change_1 ON (change_1.change_from_unit_code = inventory_product_unit_code AND change_1.change_to_unit_code='$unit_code' AND change_1.change_product_code=inventory_product_code) "; //san pham trong kho
			$select .= " LEFT JOIN tbl_change change_2 ON (change_2.change_from_unit_code = product_instore_unit_code AND change_2.change_to_unit_code='$unit_code' AND change_1.change_product_code=product_code) "; //san pham trong danh muc
		}
		
		//thiết lập điều kiện lấy dữ liệu
		if ($adminAction != '')
		{
			//do du lieu vao form
			$form->isValid ( $request->getParams () );
			
			//câu query
			$where = " WHERE inventory_quantity>0 ";
			if ($inventory_product_code != '')
				$where .= " AND `inventory_product_code` = '$inventory_product_code' ";
			if ($product_group_code != '')
				$where .= " AND `product_group_code` = '$product_group_code' ";
			if ($inventory_lot_code != '')
				$where .= " AND `inventory_lot_code` LIKE '%$inventory_lot_code%' ";
			if ($warehouse_group_code != "")
				$query .= " AND product_warehouse_group_code='$warehouse_group_code' ";
		}
		
		//thiết lập thứ tự lấy dữ liệu
		$order = $this->_helper->common->getOrderString ( array (), array ('inventory_product_code ASC' ) );
		
		//lấy dữ liệu
		$query = $select . $where . $order;
		$inventory = new Admin_Model_Inventory ();
		$data = $inventory->queryBase ( $query );
		
		//phân trang
		$data = $this->_helper->common->paginate ( $data );
		
		//Kiem tra xem phai truong hop xuat du lieu excel
		if ($request->isPost () && ($layout != ''))
			return $this->_forward ( 'report', 'index', 'report', array ('type' => 'inventory', 'paginator' => $data ) );
		
		//truyền dữ liệu ra view
		$this->view->inventory_status = $inventory_status;
		$this->view->inventory_product_code = $inventory_product_code;
		$this->view->inventory_lot_code = $inventory_lot_code;
		$this->view->form = $form;
		$this->view->unit_code = $unit_code;
		$this->view->inventory_lot_code = $inventory_lot_code;
	}

	//bao cao hh sap het han su dung
	public function expireProductAction()
	{
		//lấy tham số
		$request = $this->_request;
		$layout = $request->getParam ( 'layout', '' );
		$adminAction = $request->getParam ( 'adminAction', '' );
		$inbound_create_time_a = $request->getParam ( 'inbound_create_time_a', '' );
		$inbound_create_time_b = $request->getParam ( 'inbound_create_time_b', '' );
		$inbound_detail_product_code = $request->getParam ( 'inbound_detail_product_code', '' );
		$product_group_code = $request->getParam ( 'product_group_code', '' );
		$inventory_location_code = $request->getParam ( 'inventory_location_code', '' );
		$warehouse_code = $request->getParam ( 'warehouse_code', '' );
		
		//tạo form
		$form = new Report_Form_ExpireProduct ( $this );
		
		//lấy danh sách hàng hóa
		$product = new Admin_Model_Product ();
		$select_products = $product->fetchAllBase ( null, "product_code ASC" );
		$form->inbound_detail_product_code->setMultiOptions ( array ('' => '- Chọn -' ) + $product->toArray2 ( $select_products, "product_code", "\$row->product_code - \$row->product_name" ) );
		
		//select kho
		$warehouse = new Admin_Model_Warehouse ();
		$warehouses = $warehouse->fetchAllBase ( null, "warehouse_code ASC" );
		$form->warehouse_code->setMultiOptions ( array ('' => "- Chọn -" ) + $warehouse->toArray2 ( $warehouses, 'warehouse_code', '$row->warehouse_code - $row->warehouse_name' ) );
		
		//select nhóm hàng hóa
		$product_group = new Admin_Model_ProductGroup ();
		$options = $product_group->fetchOptions ( $product_group->fetchGroups () );
		$form->product_group_code->setMultiOptions ( array ('' => '- Chọn -' ) + $options );
		$form->product_group_code->setLabel ( "Nhóm hàng hóa" );
		
		//select nhóm kho
		$form->warehouse_group_code->setMultiOptions ( array ('' => "- Chọn -" ) + Zend_Registry::get ( 'warehouse-group' )->warehouse_group_code->toArray () );
		
		//vi trí lưu kho
		$location = new Admin_Model_Location ();
		$locations = $location->fetchAllBase ( null, "location_code ASC" );
		$form->inventory_location_code->setMultiOptions ( array ('' => "- Chọn -" ) + $location->toArray2 ( $locations, "location_code", "\$row->location_code - \$row->location_name" ) );
		
		//xử lí kho khi post dữ liệu
		if ($this->getRequest ()->isPost () /* $adminAction != '' */)
		{
			//do du lieu vao form
			$form->isValid ( $request->getParams () );
			
			//dầu ngày , cuối ngày
			$first_time = $this->_helper->time->first ( $inbound_create_time_a );
			$last_time = $this->_helper->time->last ( $inbound_create_time_b );
			
			//câu query lấy dữ liệu
			$query = "
			SELECT 
				product_code,
				product_name,
				product_group_name,
				inventory_quantity,
				inventory_location_code,
				inventory_product_time,
				inventory_expire_time
			FROM tbl_inventory
			INNER JOIN tbl_product ON product_code=inventory_product_code
			INNER JOIN tbl_product_group ON product_product_group_code= product_group_code
			INNER JOIN tbl_location ON location_code= inventory_location_code
			WHERE (inventory_expire_time BETWEEN $first_time AND $last_time)
			AND inventory_quantity>0
			";
			if ($product_group_code != "")
				$query .= " AND product_product_group_code='$product_group_code' ";
			if ($inbound_detail_product_code != "")
				$query .= " AND product_code='$inbound_detail_product_code' ";
			if ($inventory_location_code != "")
				$query .= " AND inventory_location_code='$inventory_location_code' ";
			if ($warehouse_group_code != "")
				$query .= " AND product_warehouse_group_code='$warehouse_group_code' ";
			if ($warehouse_code != "")
				$query .= " AND location_warehouse_code='$warehouse_code' ";
			$query .= " ORDER BY product_code ASC ";
			
			//lấy dữ liệu
			$inventory = new Admin_Model_Inventory ();
			$inventorys = $inventory->queryBase ( $query );
			//Phan trang
			$data = $this->_helper->common->paginate ( $inventorys );
			
			///////////////////////////////////////////////////	
			//Kiem tra xem phai truong hop xuat du lieu excel//
			/////////////////////////////////////////////////// 
			if ($layout != '')
			{
				return $this->_forward ( 'report', 'index', 'report', array ('type' => 'expire-product', 'paginator' => $data, 'from_time' => $inbound_create_time_a, 'to_time' => $inbound_create_time_b ) );
			}
		}
		
		//truyền dữ liệu ra view
		$this->view->form = $form;
	}

	//bao cao luu tru kho
	public function locationContainAction()
	{
		//lấy tham số
		$request = $this->_request;
		$layout = $request->getParam ( 'layout', '' );
		$adminAction = $request->getParam ( 'adminAction', '' );
		$location_code = $request->getParam ( 'location_code', '' );
		$warehouse_code = $request->getParam ( 'warehouse_code', '' );
		$unit_code = $request->getParam ( 'unit_code', '' );
		
		//layout
		if ($layout != '')
			$this->getHelper ( 'Layout' )->setLayout ( $layout );
		
		//tạo form
		$form = new Report_Form_LocationContain ( $this );
		
		//select nhóm kho
		$warehouse = new Admin_Model_Warehouse ();
		$warehouses = $warehouse->fetchAllBase ( null, "warehouse_code ASC" );
		$form->warehouse_code->setMultiOptions ( array ('' => "- Chọn -" ) + $warehouse->toArray2 ( $warehouses, 'warehouse_code', '$row->warehouse_code - $row->warehouse_name' ) );
		
		//vi trí lưu kho
		$location = new Admin_Model_Location ();
		$locations = $location->fetchAllBase ( null, "location_code ASC" );
		$form->location_code->setMultiOptions ( array ('' => "- Chọn -" ) + $location->toArray2 ( $locations, 'location_code', '$row->location_code - $row->location_name' ) );
		
		//select dvt
		$unit = new Admin_Model_Unit ();
		$units = $unit->fetchAllBase ( null, "unit_code ASC" );
		$form->unit_code->setMultiOptions ( array ('' => '- Chọn -' ) + $unit->toArray2 ( $units, "unit_code", "\$row->unit_code - \$row->unit_name" ) );
		
		//xử lí kho khi post dữ liệu
		if ($adminAction != '' )
		{
			//do du lieu vao form
			if ($form->isValid ( $request->getParams () ))
			{
				//câu query lấy dữ liệu
				$query = "
				SELECT 
					warehouse_name,
					location_name,
					product_code,
					product_name,
					location_contain_product_code,
					
					(CASE COALESCE(change_1.change_rate,0) WHEN 0 THEN CONCAT(location_contain_unit_code,'->$unit_code')    
					ELSE COALESCE(change_1.change_rate,0) * location_contain_max END) AS location_contain_max ,
										
					(CASE COALESCE(inventory_quantity,0) WHEN 0 THEN 0    
					ELSE 
						(CASE COALESCE(change_2.change_rate,0) WHEN 0 THEN CONCAT(inventory_product_unit_code,'->$unit_code')
						ELSE COALESCE(inventory_quantity,0) * COALESCE(change_2.change_rate,-1) END)
					END ) AS inventory_quantity
				FROM
				    tbl_location
				    INNER JOIN tbl_warehouse 
				        ON (location_warehouse_code = warehouse_code)
				    INNER JOIN tbl_location_contain 
				        ON (location_contain_location_code = location_code)
				    INNER JOIN tbl_product 
				        ON (location_contain_product_code = product_code)
				    LEFT JOIN tbl_inventory 
				    	ON (inventory_product_code=location_contain_product_code)
				    LEFT JOIN tbl_change change_2
				    	ON (change_2.change_from_unit_code=inventory_product_unit_code AND change_2.change_to_unit_code='$unit_code' AND change_2.change_product_code=inventory_product_code)				    
				    LEFT JOIN tbl_change change_1
				    	ON (change_1.change_from_unit_code=location_contain_unit_code AND change_1.change_to_unit_code='$unit_code' AND change_1.change_product_code=location_contain_product_code)
				WHERE 1				 
				";
				if ($location_code != "")
					$query .= " AND location_code='$location_code' ";
				if ($warehouse_code != "")
					$query .= " AND warehouse_code='$warehouse_code' ";
				$query .= "
				GROUP BY warehouse_code,location_code,product_code,product_name,location_contain_product_code
				ORDER BY warehouse_code,location_code,product_code ASC ";
				
				//lấy dữ liệu
				$location_contain = new Admin_Model_LocationContain ();
				$location_contains = $location_contain->queryBase ( $query );
				$data = $this->_helper->common->paginate ( $location_contains );
				
				//Kiem tra xem phai truong hop xuat du lieu excel//
				if ($layout != '')
					return $this->_forward ( 'report', 'index', 'report', array ('type' => 'location-contain', 'paginator' => $data ) );
			}
		}
		
		//truyền dữ liệu ra view
		$this->view->unit_code = $unit_code;
		$this->view->form = $form;
	}

	//bao cao xuat nhap ton tong hop
	public function masterInventoryAction()
	{
		
		//lấy tham số
		$request = $this->_request;
		$layout = $request->getParam ( 'layout', '' );
		$adminAction = $request->getParam ( 'adminAction', '' );
		$inbound_create_time_a = $request->getParam ( 'inbound_create_time_a', '' );
		$inbound_create_time_b = $request->getParam ( 'inbound_create_time_b', '' );
		$inbound_detail_product_code = $request->getParam ( 'inbound_detail_product_code', '' );
		$inbound_warehouse_group_code = $request->getParam ( 'inbound_warehouse_group_code' );
		$product_group_code = $request->getParam ( 'product_group_code', '' );
		$unit_code = $request->getParam ( 'unit_code', '' );
		
		//layout
		if ($layout != '')
			$this->getHelper ( 'Layout' )->setLayout ( $layout );
		
		//tạo form
		$form = new Report_Form_MasterInventory ( $this );
		
		//select dvt
		/*$unit = new Admin_Model_Unit();
		$units = $unit->fetchAllBase ( null, "unit_code ASC" );
		$form->unit_code->setMultiOptions ( array ('' => '- Chọn -' ) + $unit->toArray2 ( $units, "unit_code", "\$row->unit_code - \$row->unit_name" ) );*/
		
		//lấy danh sách hàng hóa
		$product = new Admin_Model_Product ();
		$select_products = $product->fetchAllBase ( "product_warehouse_group_code='$inbound_warehouse_group_code'", "product_code ASC" );
		$form->inbound_detail_product_code->setMultiOptions ( array ('' => '- Chọn -' ) + $product->toArray2 ( $select_products, "product_code", "\$row->product_code - \$row->product_name" ) );
		$form->inbound_detail_product_code->setLabel ( ucfirst ( $this->string_warehouse_group_name ) );
		
		//select nhóm hàng hóa
		$product_group = new Admin_Model_ProductGroup ();
		$options = $product_group->fetchOptions ( $product_group->fetchGroups ( '', '', " AND product_group_warehouse_group_code='$inbound_warehouse_group_code'" ) );
		$form->product_group_code->setMultiOptions ( array ('' => '- Chọn -' ) + $options );
		$form->product_group_code->setLabel ( "Nhóm " . $this->string_warehouse_group_name );
		
		//xử lí kho khi post dữ liệu
		$products = array ();
		if ($adminAction != '')
		{
			//do du lieu vao form
			$form->isValid ( $request->getParams () );
			
			//câu query lấy dữ liệu		
			$first_time = $this->_helper->time->first ( $inbound_create_time_a );
			$last_time = $this->_helper->time->last ( $inbound_create_time_b );
			$sql = "
			SELECT 
				product_code,
				product_name,
				inbound_detail_product_unit_code,
				SUM(
					CASE WHEN inbound_create_time BETWEEN 0 AND ($first_time-1) THEN 
						(CASE inbound_type WHEN 0 THEN inbound_detail_product_quantity ELSE -inbound_detail_product_quantity END)
					ELSE 0 END
				) as ton_dau_ki,
				SUM(
					CASE WHEN inbound_create_time BETWEEN $first_time AND $last_time THEN					
						(CASE inbound_type WHEN 0 THEN inbound_detail_product_quantity ELSE 0 END)
					ELSE 0 END
				) as nhap_trong_ki,
				SUM(
					CASE WHEN inbound_create_time BETWEEN $first_time AND $last_time THEN					
						(CASE inbound_type WHEN 0 THEN 0 ELSE inbound_detail_product_quantity END)
					ELSE 0 END
				) as xuat_trong_ki
			FROM tbl_inbound INNER JOIN tbl_inbound_detail ON inbound_code=inbound_detail_inbound_code
			INNER JOIN tbl_product ON product_code=inbound_detail_product_code
			WHERE inbound_create_time <= $last_time
			AND product_warehouse_group_code='$inbound_warehouse_group_code' ";
			if ($product_group_code != "")
				$sql .= " AND product_product_group_code='$product_group_code' ";
			if ($inbound_detail_product_code != "")
				$sql .= " AND product_code='$inbound_detail_product_code' ";
			$sql .= "GROUP BY product_code,product_name,inbound_detail_product_unit_code
			ORDER BY product_code,inbound_detail_product_unit_code";
			
			//lấy dữ liệu
			$product = new Admin_Model_Product ();
			$products = $product->queryBase ( $sql );
			$data = $this->_helper->common->paginate ( $products );
			
			//xử lí dữ liệu
			foreach ( $data as $row )
				$row->m_Array ['ton_cuoi_ki'] = $row->m_Array ['ton_dau_ki'] + $row->m_Array ['nhap_trong_ki'] - $row->m_Array ['xuat_trong_ki'];
		}
		
		//Kiem tra xem phai truong hop xuat du lieu excel//
		if ($request->isPost () && ($layout != ''))
		{
			$tmp_data = array ('type' => 'master-inventory', 'string_warehouse_group_name' => $this->string_warehouse_group_name, 'paginator' => $data, 'from_time' => $inbound_create_time_a, 'to_time' => $inbound_create_time_b );
			return $this->_forward ( 'report', 'index', 'report', $tmp_data );
		}
		
		//truyền dữ liệu ra view
		$this->view->form = $form;
		$this->view->products = $data;
	}

	//bao cao xuat nhap ton chi tiet
	public function detailInventoryAction()
	{
		//lấy tham số
		$request = $this->_request;
		$layout = $request->getParam ( 'layout', '' );
		$adminAction = $request->getParam ( 'adminAction', '' );
		$inbound_create_time_a = $request->getParam ( 'inbound_create_time_a', '' );
		$inbound_create_time_b = $request->getParam ( 'inbound_create_time_b', '' );
		$inbound_detail_product_code = $request->getParam ( 'inbound_detail_product_code', '' );
		$product_group_code = $request->getParam ( 'product_group_code', '' );
		$inbound_warehouse_group_code = $request->getParam ( 'inbound_warehouse_group_code', '' );
		
		//layourt
		if ($layout != '')
			$this->getHelper ( 'layout' )->setLayout ( $layout );
		
		//tạo form
		$form = new Report_Form_MasterInventory ( $this );
		
		//lấy danh sách hàng hóa
		$product = new Admin_Model_Product ();
		$select_products = $product->fetchAllBase ( "product_warehouse_group_code='$inbound_warehouse_group_code'", "product_code ASC" );
		$form->inbound_detail_product_code->setMultiOptions ( array ('' => '- Chọn -' ) + $product->toArray2 ( $select_products, "product_code", "\$row->product_code - \$row->product_name" ) );
		$form->inbound_detail_product_code->setLabel ( ucfirst ( $this->string_warehouse_group_name ) );
		
		//select nhóm hàng hóa
		$product_group = new Admin_Model_ProductGroup ();
		$options = $product_group->fetchOptions ( $product_group->fetchGroups ( '', '', " AND product_group_warehouse_group_code='$inbound_warehouse_group_code'" ) );
		$form->product_group_code->setMultiOptions ( array ('' => '- Chọn -' ) + $options );
		$form->product_group_code->setLabel ( "Nhóm " . $this->string_warehouse_group_name );
		
		//lấy danh sách lí do
		$reason = new Admin_Model_Reason ();
		$reasons = $reason->fetchAllBase ( " reason_warehouse_group_code='$inbound_warehouse_group_code' ", array ("reason_inbound_type ASC" ) );
		$in_count = $reason->countBase ( " reason_warehouse_group_code='$inbound_warehouse_group_code' AND reason_inbound_type='0' " );
		$out_count=count($reasons)-$in_count;
		
		//câu query lấy dữ liệu
		if ($this->getRequest ()->isPost ())
		{
			$form->isValid ( $request->getParams () );
			
			//câu query lấy dữ liệu		
			$first_time = $this->_helper->time->first ( $inbound_create_time_a );
			$last_time = $this->_helper->time->last ( $inbound_create_time_b );
			$sql = "
			SELECT 
				inbound_detail_product_code,
				product_name,
				inbound_detail_product_unit_code,
				SUM(
					CASE WHEN inbound_create_time BETWEEN 0 AND ($first_time-1) THEN 
						(CASE inbound_type WHEN 0 THEN inbound_detail_product_quantity ELSE -inbound_detail_product_quantity END)
					ELSE 0 END
				) as `first`,";
			foreach ( $reasons as $key => $reason )
			{
				if ($key > 0)
					$sql .= ",";
				$sql .= "
				SUM(
					CASE WHEN (
						(inbound_create_time BETWEEN $first_time AND $last_time)
						AND inbound_type={$reason->reason_inbound_type}
						AND inbound_reason_code='{$reason->reason_code}'					
					) THEN inbound_detail_product_quantity ELSE 0 END
				) AS `reason_{$reason->reason_code}`";
			}
			$sql .= " FROM   
				tbl_inbound INNER JOIN tbl_inbound_detail ON inbound_code=inbound_detail_inbound_code
				INNER JOIN tbl_product ON product_code=inbound_detail_product_code			
			WHERE inbound_create_time <= $last_time
			AND product_warehouse_group_code='$inbound_warehouse_group_code' ";
			if ($product_group_code != "")
				$sql .= " AND product_product_group_code='$product_group_code' ";
			if ($inbound_detail_product_code != "")
				$sql .= " AND product_code='$inbound_detail_product_code' ";
			$sql .= "GROUP BY product_code,product_name,inbound_detail_product_unit_code
			ORDER BY product_code";
			
			//lay du lieu
			$inbound_detail = new Admin_Model_InboundDetail ();
			$inbound_details = $inbound_detail->queryBase ( $sql );
			
			//phan trang
			$data = $this->_helper->common->paginate ( $inbound_details );
			
			//Kiem tra xem phai truong hop xuat du lieu excel//
			if ($layout != '')
			{
				$tmp_data = array ('type' => 'detail-inventory', 'reasons' => $reasons, 'in_count' => $in_count, 'out_count' => $out_count, 'paginator' => $data, 'from_time' => $inbound_create_time_a, 'to_time' => $inbound_create_time_b );
				return $this->_forward ( 'report', 'index', 'report', $tmp_data );
			}
		}
		
		//truyền dữ liệu ra view
		$this->view->form = $form;
		$this->view->reasons = $reasons;
		$this->view->in_count = $in_count;
		$this->view->out_count = $out_count;
	}
}